package com.surekam.mnai.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.surekam.mnai.model.Address;

/**
 * 地址信息dao层接口实现
 * @author lium
 *
 */
@Component
public class AddressDao extends BaseDao {
	
	private static Logger logger = LoggerFactory.getLogger(AddressDao.class);
	
	private AddressMapper addressMapper = new AddressMapper();
	
	/**
	 * Address 类与表T_ADDRESS的映射关系
	 *
	 */
	private static class AddressMapper implements RowMapper<Address>{

		@Override
		public Address mapRow(ResultSet rs, int rowNum) throws SQLException {
			Address address = new Address();
			address.setAddressId(rs.getInt("ADDRESS_ID"));
			address.setProvince(rs.getString("PROVINCE"));
			address.setCity(rs.getString("CITY"));
			address.setAreaCode(rs.getString("AREA_CODE"));
			address.setPostCode(rs.getString("POSTCODE"));
			return address;
		}
		
	}
	
	/**
	 * 按所在省份查询
	 * @param province 所要查询的省份
	 * @return 所在省份的地址信息
	 */
	public List<Address> queryByProvince(String province){
		String querySql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE PROVINCE = ?";
		return this.jdbcTemplate.query(querySql, addressMapper, province);
	}
	
	/**按所在城市查询
	 * @param city 所要查询的城市
	 * @return 所在城市的地址信息
	 */
	public List<Address> queryByCity(String city){
		String querySql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE CITY = ?";
		return this.jdbcTemplate.query(querySql, addressMapper, city);
	}
	
	/**
	 * 按所在城市区号查询
	 * @param areaCode 所在城市的区号
	 * @return 根据区号获得的地址信息
	 */
	public List<Address> queryByAreaCode(String areaCode){
		String querySql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE AREA_CODE = ?";
		return this.jdbcTemplate.query(querySql, addressMapper, areaCode);
	}
	
	/**
	 * 按所在城市邮政编码查询
	 * @param postCode 所在城市的邮政编码
	 * @return 根据邮政编码获得的地址信息
	 */
	public List<Address> queryByPostCode(String postCode){
		String querySql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE POSTCODE = ?";
		return this.jdbcTemplate.query(querySql, addressMapper, postCode);
	}
	
	/**
	 * 根据省份获取所有该省份下的城市名称列表
	 * @param province 指定的省份
	 * @return 所有该省份下的城市名称列表
	 */
	public List<Address> queryCity(String province){
		String querySql = "SELECT CITY FROM t_address WHERE PROVINCE = ?";
		return this.jdbcTemplate.query(querySql, addressMapper, province);
	}
	
	/**
	 * 根据省份和城市获取匹配的Address对象
	 * @param province 所指定的省份
	 * @param city 所指定的城市
	 * @return 符合匹配的地址信息
	 */
	public Address query(String province,String city){
		String querySql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE PROVINCE = ? AND CITY = ?";
		return this.jdbcTemplate.queryForObject(querySql, addressMapper, province, city);
	}
	
	/**
	 * 添加一条地址信息
	 * @param address 所有添加的信息
	 */
	public void insert(Address address){
		if(address == null){
			throw new IllegalArgumentException("Address实例不能为null");
		}
		
		logger.info("插入记录到ADDRESS表中..");
		String insertSql = "INSERT INTO t_address(ADDRESS_ID," + "PROVINCE," +
				"CITY," + "AREA_CODE," + "POSTCODE) VALUES(?,?,?,?,?)";
		this.jdbcTemplate.update(insertSql, address.getAddressId(),address.getProvince(),
				address.getCity(),address.getAreaCode(),address.getPostCode());
		
		logger.info("插入记录到ADDRESS表中..");
	}
	
	/**
	 * 删除一条地址信息
	 * @param id
	 */
	public void deleteById(int id){
		logger.info("删除Address表中的记录:addressId=" + id);
		
		String deleteSql = "DELETE FROM t_address WHERE ADDRESS_ID = ?";
		this.jdbcTemplate.update(deleteSql, id);
		
		logger.info("删除Address表中的记录:addressId=" + id + "成功!");
	}
	
	/**
	 * 修改地址信息
	 * @param address
	 */
	public void update(Address address){
		if(address == null){
			throw new IllegalArgumentException("Address实例不能为空!");
		}
		
		logger.info("更新Address表中的记录: addressId" + address.getAddressId());
		
		String updateSql = "UPDATE t_address  SET PROVINCE = ?,CITY = ?,AREA_CODE = ?,POSTCODE = ? WHERE ADDRESS_ID = ?";
		this.jdbcTemplate.update(updateSql, address.getProvince(),address.getCity(),address.getAreaCode(),
				address.getPostCode(),address.getAddressId());
		
	}

	/**
	 * 查询数据根据id
	 * @param id
	 */
	public Address queryById(String id) {
		logger.info("查询Address表中的记录: addressId" + id);
		
		String selectSql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address WHERE ADDRESS_ID = ?";
		return this.jdbcTemplate.queryForObject(selectSql, addressMapper, id);
	}
	
	/**
	 * 返回所有的地址信息
	 * @return
	 */
	public List<Address> queryAll(){
		logger.info("查询Address表中的所有记录");
		 //where 1==1 || 2 = ? 这句话是因为我对jdbcTemplate里面的方法调用不熟悉，所有用这个保证返回所有的数据
		String selectSql = "SELECT ADDRESS_ID, PROVINCE, CITY, AREA_CODE, POSTCODE FROM t_address where 1==1 || 2 == ?";
		return this.jdbcTemplate.query(selectSql, addressMapper,1);
	}
	
}
